package benny_driving_ser.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import benny_driving_ser.dao.CardDao;
import benny_driving_ser.entity.Card;
import benny_driving_ser.util.DBUtil;


/**
 * 卡的增删改查
 * @author Administrator
 *
 */
public class CardDaoImpl implements CardDao {

	
	//增
	public int addCard(Card card) {
		
		int isSave = 0;
		
		//INSERT into t_card(company,isactivated,surplus,number,verify_num) values('驾车宝典',0,3,'sh000311','0000.')
		String sql = "insert into t_card(company,isactivated,times,number,verif y_num,card_state,card_mil,card_cost)" +
				"VALUES(?,?,?,?,?,?,?,?);";
		try {
			Connection conn = DBUtil.getInstance().openConnection();
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, card.getCompany());
			pst.setInt(2, 1);
			pst.setInt(3, 3);
			pst.setString(4, card.getNumber());
			pst.setString(5, card.getVerify_num());
			isSave = pst.executeUpdate();
			pst.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		System.out.println("是否保存? 1 为保存,0 为未保存."+isSave);
		return isSave ==0? 0:1;
	}

	//删
	public int delCard(int id) {
		
		int isDel = 0;
		String sql = "delete from t_card where id="+id;
		try {
			Connection conn = DBUtil.getInstance().openConnection();
			PreparedStatement pst = conn.prepareStatement(sql);
			isDel = pst.executeUpdate();
			System.out.println("isDel"+isDel);
			
			pst.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		//System.out.println("0为已删除,1为未删除"+isDel);
		return isDel == 0?1:0;
	}

	//加载全部该公司全部卡
	public List<Card> loadAllCard(String company) {
		List<Card> list = new ArrayList();
		String sql = "select * from t_card where company like '%"+company+"%'";
		Connection conn = DBUtil.getInstance().openConnection();
		;
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Card card = new Card();
				card.setId(rs.getInt("id"));
				card.setCompany(rs.getString("company"));
				card.setIsActivated(rs.getInt("isActivated"));
				card.setSurplus(rs.getInt("surplus"));
				card.setNumber(rs.getString("number"));
				card.setVerify_num(rs.getString("verify_num"));
				list.add(card);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 
		return list.size()==0?null:list;
	}

	//查询单个
	public Card findCardById(int id) {
		
		String sql = "select * from t_card where id="+id;
		Card card = new Card();
		Connection conn = DBUtil.getInstance().openConnection();
		PreparedStatement pst;
		try {
			pst = conn.prepareStatement(sql);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				card.setId(rs.getInt("id"));
				card.setCompany(rs.getString("company"));
				card.setIsActivated(rs.getInt("isActivated"));
				card.setSurplus(rs.getInt("surplus"));
				card.setNumber(rs.getString("number"));
				card.setVerify_num(rs.getString("verify_num"));
			}
			rs.close();
			pst.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
		return card;
	}

	
	public static void main(String[] args) {
//		Card card = new Card();
//		card.setCompany("长城代驾");
//		card.setIsActivated(1);
//		card.setSurplus(3);
//		card.setNumber("sh000110");
//		card.setVerify_num("0000");
		
//		Card card = findCardById(6);
		
		
//		addCard(card);
		
//		List<Card> list = loadAllCard("长城");
//		System.out.println(list.toString());
//		System.out.println("这里打印来的..."+card.toString());
		//delCard(card.getId());
	}
}
